import pymysql
import pandas as pd
import asyncio
import time
import datetime
import traceback
import os
import sys
import numpy as np
import pytz
# print(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
# # 添加导包路径
# sys.path.append(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
sys.path.append(r"D:\my_work\ac_lingxing_api\ac_lingxing_api")
from multiprocessing import  Process, Queue, Pool
from bin.openapi import OpenApiBase
from sqlalchemy import create_engine
from itertools import product
from itertools import islice
from lxml import etree
import re
import demoji
from loguru import logger
logger.add(r"D:\my_work\ac_lingxing_api\ac_lingxing_api\api_email\email_message.log")


class tools:
    
    @staticmethod
    def get_engine(database):
        engine = create_engine(f'mysql+pymysql://user1:123456@192.168.8.10:3306/{database}?charset=utf8mb4')
        return engine

    @staticmethod
    def update_detail(webmail_uuid_str):
        query = f"update lx_email_detail set is_ana = 1 where webmail_uuid in ({webmail_uuid_str})"
        conn = pymysql.connect(user='user1', password='123456', host='192.168.8.10', database='rpa_email_task')
        cur = conn.cursor()
        cur.execute(query)
        conn.commit()
        cur.close()
        conn.close()
        
    @staticmethod
    def html_text_asin(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        element = etree.HTML(text_html)
        try:
            data_asin = re.findall("B0[0-9A-Z]{8}", text_html)
            if len(data_asin)>0:
                return data_asin[0]
        except:
            return ""
        
    @staticmethod
    def html_text_order(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        # print(row_data['webmail_uuid'])
        element = etree.HTML(text_html)
        try:
            if from_address == "donotreply@amazon.com":
                order = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p[4]')[0].text.strip()
            else:
                order = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p[2]')[0].text.strip()
            pattern = re.compile(r'[0-9]+-[0-9]+-[0-9]+')
            order_num = pattern.findall(order)[0]
        except:
            order_num = ""
        return order_num
    @staticmethod
    def html_text_message(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        element = etree.HTML(text_html)
        if text_html == "":
            message = row_data['text_plain']
        else:
            try:
                
                if from_address == "donotreply@amazon.com":
                    message = element.xpath('//pre')[0].text.strip()
                else:
                    message = element.xpath('//pre')[0].text.strip()
            except:
                logger.info(row_data['webmail_uuid'])
                try:
                    message_ele = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p')[5]
                except:
                    
                    try:
                        message_ele = element.xpath("//div[@dir='auto']")[0]
                    except:
                        try:
                            message_ele = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/table[2]/tbody/tr/th/p[1]')[0]
                        except:
                            try:
                                message_ele = element.xpath('/html/body')[0]
                            except:
                                logger.error("报错ID", row_data['webmail_uuid'])
                                logger.error(traceback.format_exc())
                            
                        
                message = etree.tostring(message_ele, encoding='unicode', method='html').replace("<p>", "").replace("<br>", "").replace("</p>", "").strip()
        return message

class message_handler:

    def __init__(self):
        self.table_name = 'lx_email_message'
        self.engine = tools.get_engine("rpa_email_task")
        self.update_time = pd.to_datetime(datetime.datetime.now())


    def get_message_html(self):
        
        query = "select webmail_uuid, subject, from_name, from_address, to_address_all, date, text_html, text_plain, type, email_account from lx_email_detail where is_ana = 0 and email_type='买家消息'"
        html_df = pd.read_sql(query, con=self.engine)
        if len(html_df)>0:
            html_df['message'] = html_df.apply(tools.html_text_message, axis=1)
            html_df['order_num'] = html_df.apply(tools.html_text_order, axis=1)
            html_df['detail_asin'] = html_df.apply(tools.html_text_asin, axis=1)
            html_df['update_time'] = self.update_time
            html_df.to_sql(self.table_name, con=self.engine, if_exists="append", index=False)
            webmail_uuid_str = ",".join(["'" + webmail_uuid + "'" for webmail_uuid in html_df['webmail_uuid'].to_list()])
            tools.update_detail(webmail_uuid_str)
            
            logger.info(f"数据已插入")
        else:
            logger.info("无数据")
    

def main():
    mh = message_handler()
    mh.get_message_html()
    
if __name__ == "__main__":  
    main()